class MigrateFolderContents < ActiveRecord::Migration

  class << self
    #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    # Database create/drop methods
    #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   
    def up
      create_folder_contents_table
      update_folder_data
      update_function_data
      update_page_data
    end

    def down
      add_page_data
      add_function_data
      add_folder_data
      drop_folder_contents_table
    end
    
    private
      #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      # Table create/drop methods
      #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      
      def create_folder_contents_table
        create_table_with_privies(:folder_contents, :force => true) do |t|
          t.column("folder_id", :integer, :null => false)
          t.column("page_id", :integer)
          t.column("function_id", :integer)
          t.column("sub_folder_id", :integer)
          t.column("seq", :integer, :default => 1)
        end

        foreign_key(:folder_contents, :folder_id, :folders)
        foreign_key(:folder_contents, :page_id, :pages)
        foreign_key(:folder_contents, :function_id, :functions)
        foreign_key(:folder_contents, :sub_folder_id, :folders)
      end

      def drop_folder_contents_table
        drop_table(:folder_contents)
      end

      def update_folder_data
        parents = Folder.find(:all, :conditions => "parent_id is null")
        add_column(:folders,:top_level,:boolean, :default => false)
        toplvl = Folder.create(:name => "top_level", :top_level => true, :updated_by => 1)
        parents.each do |parent|
          FolderContent.create(:folder_id => toplvl.id,
                               :sub_folder_id => parent.id,
                               :seq => parent.seq)
          children = Folder.find(:all, :conditions => "parent_id = #{parent.id}")
          children.each do |child|
            FolderContent.create(:folder_id => parent.id,
                                 :sub_folder_id => child.id,
                                 :seq => child.seq)
            grand_children = Folder.find(:all, :conditions => "parent_id = #{child.id}")
            grand_children.each do |grand_child|
              FolderContent.create(:folder_id => child.id,
                                   :sub_folder_id => grand_child.id,
                                   :seq => grand_child.seq)
            end
          end
        end
       remove_column(:folders,:parent_id)
       remove_column(:folders,:seq)
      end

      def update_function_data
        qr = query("select folder_id,function_id from folders_functions")
        qr.each do |row|
            seq = FolderContent.get_next_seq(row[0].to_s.to_i)
            FolderContent.create(:folder_id => row[0].to_s.to_i,:function_id => row[1].to_s.to_i, :seq => seq)
        end
        drop_lookup_table(:folders,:functions)
      end

      def update_page_data
        pages = Page.find(:all, :order => "title")
        pages.each do |page|
          seq = FolderContent.get_next_seq(page.folder_id)
          FolderContent.create(:folder_id => page.folder_id, :page_id => page.id, :seq => seq)
        end
        remove_column(:pages,:folder_id)
      end

      def add_page_data
        add_column(:pages, :folder_id, :integer)
        foreign_key(:pages, :folder_id, :folders)
        fcs = FolderContent.find(:all, :conditions => "page_id is not null")
        fcs.each do |fc|
          pg = Page.find(fc.page_id) 
          pg.folder_id = fc.folder_id
          pg.save
        end
      end

      def add_function_data
        create_lookup_table(:folders, :functions)
        fcs = FolderContent.find(:all, :conditions => "function_id is not null")
        fcs.each do |fc|
          execute("insert into folders_functions(folder_id,function_id) values(#{fc.folder_id},#{fc.function_id})")
        end
      end

      def add_folder_data
        add_column(:folders,:parent_id,:integer)
        add_column(:folders,:seq,:integer)
        remove_column(:folders,:top_level)
        foreign_key(:folders, :parent_id, :folders)
        slfs = FolderContent.find(:all, :conditions => "sub_folder_id is not null")
        slfs.each do |slf|
          fldr = Folder.find(slf.sub_folder_id)
          fldr.parent_id = slf.folder_id
          fldr.seq = slf.seq
          fldr.save
        end
      end
  end #end class block
end
